/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Controller;

import Model.login;
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.http.HttpSession;

/**
 *
 * @author Martin Spencer Sy
 */
public class CustomerManager {
    private static Connection con = null;
    private static Statement s = null;
    private static String sql = null;
    private static ResultSet rs = null;
    
    public static int addUser(String username, String password, String email, 
            String lname, String fname, String mname, String address, String country, String zip){
        
        PreparedStatement ps;
        int insertResult;
        
        try {
            
            Class.forName("com.mysql.jdbc.Driver");
            
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/foobar", 
                    "root", "");
            
            sql = "CALL checkUsername(?)";
            ps = con.prepareStatement(sql);
            ps.setString(1, username);
            rs = ps.executeQuery();
            
            if( !(rs.next()) ){//if the username is not yet taken
                
                sql = "CALL addUser(?, ?, ?, ?, ?, ?, ?, ?, ?)";

                ps = con.prepareStatement(sql);

                ps.setString(1, username);
                ps.setString(2, password);
                ps.setString(3, email);
                ps.setString(4, lname);
                ps.setString(5, fname);
                ps.setString(6, mname);
                ps.setString(7, address);
                ps.setString(8, zip);
                ps.setString(9, country);

                //execute the instert statement
                insertResult = ps.executeUpdate();

                //check if the user was successfully added
                if( insertResult == 1 ){
                    con.close();
                    return 1;
                }
                else{
                    con.close();
                    return -1;
                }
            }
            else{//if the username is already taken
                return 2;
            }
        } 
        catch (SQLException ex) {
            //Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);
            LogManager.logEvent(CustomerManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, null);
        }
        catch (ClassNotFoundException e1) {
            // JDBC driver class not found, print error message to the console
            System.out.println(e1.toString());
            LogManager.logEvent(CustomerManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Class Not Found Exception: " + e1.getMessage(), null, null);
        }
        catch (Exception e3) {
            // other unexpected exception, print error message to the console
            System.out.println(e3.toString());
            LogManager.logEvent(CustomerManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Exception: " + e3.getMessage(), null, null);
        }
        
        return -1;
    }
    
    public static boolean editInfo(String email, String lname, String fname, 
            String mname, String add, String zip, String country, HttpSession session){
        
        PreparedStatement ps = null;
        int updateResult,
            user;
        
        try{
            Class.forName("com.mysql.jdbc.Driver");
            
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/foobar", 
                    "root", "");
            
            sql = "CALL editUser(?, ?, ?, ?, ?, ?, ?, ?)";
            
            ps = con.prepareStatement(sql);
            
            user = Integer.parseInt( (String)session.getAttribute("user") );
            
            ps.setInt(1, user);
            ps.setString(2, email);
            ps.setString(3, lname);
            ps.setString(4, fname);
            ps.setString(5, mname);
            ps.setString(6, add);
            ps.setString(7, zip);
            ps.setString(8, country);
            
            updateResult = ps.executeUpdate();
            
            if(updateResult == 1){
                con.close();
                return true;
            }
            else{
                con.close();
                return false;
            }
            
        }
        catch (SQLException ex) {
            //Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);
            LogManager.logEvent(CustomerManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, null);
        }
        catch (ClassNotFoundException e1) {
            // JDBC driver class not found, print error message to the console
            System.out.println(e1.toString());
            LogManager.logEvent(CustomerManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Class Not Found Exception: " + e1.getMessage(), null, null);
        }
        catch (Exception e3) {
            // other unexpected exception, print error message to the console
            System.out.println(e3.toString());
            LogManager.logEvent(CustomerManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Exception: " + e3.getMessage(), null, null);
        }
        
        return false;
    }
    
    public static int changePassword(String oldPassword, String newPassword, HttpSession session){
        PreparedStatement ps = null;
        String username,
                userID;
        int updateResult;
        
        try{
            Class.forName("com.mysql.jdbc.Driver");
            
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/foobar", 
                    "root", "");
            
            sql = "CALL loginUser(?, ?)";
            
            ps = con.prepareStatement(sql);
            
            username = (String)session.getAttribute("name");
            
            ps.setString(1, username);
            ps.setString(2, oldPassword);
            
            rs = ps.executeQuery();
            
            if( rs.next() ){
                sql = "CALL changePW(?, ?)";
            
                ps = con.prepareStatement(sql);
                
                userID = (String)session.getAttribute("user");
                
                ps.setInt(1, Integer.parseInt(userID));
                ps.setString(2, newPassword);

                updateResult = ps.executeUpdate();

                if( updateResult == 1 ){
                    con.close();
                    rs.close();
                    return 1;
                }
                else{
                    con.close();
                    rs.close();
                    return -2;
                }
            }
            else{
                con.close();
                rs.close();
                return 0;
            }
            
        }
        catch (SQLException ex) {
            //Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);
            LogManager.logEvent(CustomerManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, null);
        }
        catch (ClassNotFoundException e1) {
            // JDBC driver class not found, print error message to the console
            System.out.println(e1.toString());
            LogManager.logEvent(CustomerManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Class Not Found Exception: " + e1.getMessage(), null, null);
        }
        catch (Exception e3) {
            // other unexpected exception, print error message to the console
            System.out.println(e3.toString());
            LogManager.logEvent(CustomerManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Exception: " + e3.getMessage(), null, null);
        }
        
        return -2;
    }
    
    public static int forgotPW(String username, String email){
        PreparedStatement ps = null;
        
        try{
            Class.forName("com.mysql.jdbc.Driver");
            
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/foobar", 
                    "root", "");
            
            sql = "CALL checkUsername(?)";
            ps = con.prepareStatement(sql);
            ps.setString(1, username);
            rs = ps.executeQuery();
            
            if( rs.next() ){//if the username exists
                
                //get user status
                int userStat = rs.getInt("status");
                
                sql = "CALL forgotPW(?, ?)";
                ps = con.prepareStatement(sql);
                ps.setString(1, username);
                ps.setString(2, email);
                rs = ps.executeQuery();
                
                if( rs.next() && userStat < 5 ){//if the username and email is correct and is not locked
                    sql = "CALL unlockUserAcct(?)";
                    ps = con.prepareStatement(sql);
                    ps.setString(1, username);
                    ps.executeUpdate();
                    
                    return 1;
                }
                else if(userStat < 5){//if the user exist but the email is wrong and is not yet locked out
                    sql = "CALL failedLogin(?)";
                    ps = con.prepareStatement(sql);
                    ps.setString(1, username);
                    
                    //execute twice
                    ps.executeUpdate();
                    ps.executeUpdate();
                    
                    return 0;
                }
                else if(userStat >= 5){//if the user is already locked out
                    return 2;
                }
                
            }
            else{
                return 0;
            }
            
        }
        catch (SQLException ex) {
            //Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);
            LogManager.logEvent(CustomerManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, null);
        }
        catch (ClassNotFoundException e1) {
            // JDBC driver class not found, print error message to the console
            System.out.println(e1.toString());
            LogManager.logEvent(CustomerManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Class Not Found Exception: " + e1.getMessage(), null, null);
        }
        catch (Exception e3) {
            // other unexpected exception, print error message to the console
            System.out.println(e3.toString());
            LogManager.logEvent(CustomerManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Exception: " + e3.getMessage(), null, null);
        }
        
        return -1;
    }
}